﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Business;
using WF_Business;
using WF_DataAccess;
using System.Data.OracleClient;
using Business.Struct;

namespace SbBusiness.User
{
    /// <summary>
    /// 用户权限申请
    /// </summary>
    public class SysUserRightRequest
    {
        /// <summary>
        /// 根据用户获取流程权限
        /// </summary>
        /// <param name="strUserid">用户id</param>
        /// <returns></returns>
        public DataTable GetAllRightByUser(string strUserid)
        {
            string strSql = @"select id,userid,serial_name,notes,serial_time,is_pass,flowtype,
                            (case when is_pass=1 then '已通过'else '未通过' end) pass
                             from sys_user_serial where userid = :userid";//upper(:userid)

            IDataAccess ida = SysParams.OAConnection(false);
            IDataParameter[] iDataPrams = DataFactory.GetParameter(DataFactory.DefaultDbType, 1);
            iDataPrams[0].ParameterName = "userid";
            iDataPrams[0].DbType = DbType.String;
            iDataPrams[0].Value = strUserid;

            return ida.RunSql(strSql, ref iDataPrams);
        }

        /// <summary>
        /// 保存流程权限信息
        /// </summary>
        /// <param name="strUserId">用户ID</param>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strFlowType">流程别名</param>
        /// <param name="strNotes">申请理由</param>
        /// <returns>返回流程权限ID</returns>
        public string SaveRightInfo(string strUserId, string strFlowName,string strFlowType, string strNotes)
        {
            string strNewId = string.Empty;
            string strSql = string.Format("select UserId from sys_user_serial t where UserId='{0}' and SERIAL_NAME='{1}'",
                strUserId, strFlowName);
            string strId = SysParams.OAConnection().GetValue(strSql);

            if (string.IsNullOrEmpty(strId))//沒有数据
            {
                strSql = string.Format(@"insert into sys_user_serial t(UserId,serial_name,flowtype,notes) 
                    values(:userid,:serialname,:filetype,:notes)  returning id into :newId");

                IDataAccess ida = SysParams.OAConnection(false);

                IDataParameter[] iDataPrams = new OracleParameter[5];
                iDataPrams[0] = new OracleParameter("userid", OracleType.VarChar, 255);
                iDataPrams[0].Value = strUserId;

                iDataPrams[1] = new OracleParameter("serialname", OracleType.VarChar, 255);
                iDataPrams[1].Value = strFlowName;

                iDataPrams[2] = new OracleParameter("filetype", OracleType.VarChar, 255);
                iDataPrams[2].Value = strFlowType;

                iDataPrams[3] = new OracleParameter("notes", OracleType.VarChar, 255);
                iDataPrams[3].Value = strNotes;

                iDataPrams[4] = new OracleParameter("newId", OracleType.VarChar, 50);
                iDataPrams[4].Direction = ParameterDirection.Output;

                ida.RunSql(strSql, ref iDataPrams);

                strNewId = iDataPrams[4].Value.ToString();

                //添加操作日志 addby zhongjian 20100421
                string strRemark = string.Format("新申报办理事项为 {0} 业务,新申请的ID为:{1}", strFlowType, strNewId);
                SystemLogs.AddSystemLogs(strUserId, "add", strRemark, strSql);
            }
            else
            {
                strSql = string.Format(@"update sys_user_serial t set notes='{2}' 
                    where UserId='{0}' and serial_name='{1}'", strUserId, strFlowName, strNotes);
                SysParams.OAConnection().RunSql(strSql);

                //获取修改后的ID addby zhongjian 20091112
                strSql = string.Format(@"select id from sys_user_serial where UserId='{0}' and serial_name='{1}'",strUserId,strFlowName);
                strNewId = SysParams.OAConnection().GetValue(strSql);

                //添加操作日志
                string strRemark = string.Format("修改申请办理事项为 {0} 业务内容,修改ID为:{1}", strFlowType, strNewId);
                SystemLogs.AddSystemLogs(strUserId, "update", strRemark, strSql);
            }           

            return strNewId;
        }

        /// <summary>
        /// 保存流程权限申请上传的附件
        /// </summary>
        /// <param name="fileUpload"></param>
        /// <param name="dType"></param>
        /// <returns></returns>
        public string StoreSerialAtt(FileUploadStruct fileUpload, DataStoreType dType)
        {
            IDataAccess ida = SysParams.OAConnection(false);
            string strSql = string.Empty;
            string strNewId = string.Empty;

            if (dType == DataStoreType.Insert)
            {
                strSql = string.Format(@"insert into {0}({1},{2},{3},{4}) values(:filedata,
                :fileType,:filename,:serial_id) returning id into :newId", fileUpload.TableName,
                fileUpload.FileStoreFieldName, fileUpload.FileTypeFieldName, fileUpload.StoreResourceFieldName,
                fileUpload.StoreParamFieldName);

                IDataParameter[] idp = new OracleParameter[5];

                idp[0] = new OracleParameter("filedata", OracleType.Blob);
                idp[0].Value = fileUpload.FileBytes;

                idp[1] = new OracleParameter("fileType", OracleType.VarChar, 255);
                idp[1].Value = fileUpload.FileTypeFieldValue;

                idp[2] = new OracleParameter("filename", OracleType.VarChar, 255);
                idp[2].Value = fileUpload.StoreResourceNameValue;

                idp[3] = new OracleParameter("serial_id", OracleType.VarChar, 255);
                idp[3].Value = fileUpload.StoreParamValue;

                idp[4] = new OracleParameter("newId", OracleType.VarChar, 255);
                idp[4].Direction = ParameterDirection.Output;

                ida.RunSql(strSql, ref idp);

                strNewId = idp[4].Value.ToString();
            }

            return strNewId;
        }


        /// <summary>
        /// 删除用户权限信息
        /// </summary>
        /// <param name="strId">用户权限对应关系表id</param>
        /// <returns></returns>
        public int DeleteRightInfo(string strId)
        {
            string strSql = string.Format(@"delete from sys_user_serial where id = '{0}'",strId);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 存储权限信息
        /// </summary>
        /// <param name="strUserId">用户id</param>
        /// <param name="strFlowName">流程名</param>
        /// <param name="strNotes">备注</param>
        /// <returns></returns>
        public int StoreRightInfo(string strUserId,string strFlowName,string strNotes)
        {
            string strSql = string.Format("select UserId from sys_user_serial t where UserId='{0}' and SERIAL_NAME='{1}'",
                strUserId,strFlowName);
            string strId = SysParams.OAConnection().GetValue(strSql);

            if (string.IsNullOrEmpty(strId))
            {
                strSql = string.Format(@"insert into sys_user_serial t(UserId,serial_name,notes) 
                    values('{0}','{1}','{2}')", strUserId,strFlowName,strNotes);
            }
            else
            {
                strSql = string.Format(@"update sys_user_serial t set notes='{2}' 
                    where UserId='{0}' and serial_name='{1}'", strUserId, strFlowName, strNotes);
            }
            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 根据用户和办理权限对应表id获取权限信息
        /// </summary>
        /// <param name="strId">用户和办理权限对应表id</param>
        /// <returns></returns>
        public DataTable GetAllRightById(string strId)
        {
            string strSql = string.Format(@"select * from sys_user_serial where id = upper('{0}')",strId);

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">流程权限ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091112-->
        public DataTable GetSerialAtt(string strSerialId)
        {
            string strSql = string.Format(@"select id,filedata,filetype,filename
                            from sys_user_serialatt where serial_id= upper('{0}')",strSerialId);

            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 删除流程权限的附件信息
        /// </summary>
        /// <param name="strSerialId">流程权限ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091112-->
        public int DeleteSerialAtt(string strSerialId)
        {
            string strSql = string.Format(@"delete from sys_user_serialatt where serial_id = '{0}'",strSerialId);

            return SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 获取所有已发布的流程别名
        /// </summary>
        /// <returns></returns>
        /// <!--addby zhongjian 20100315-->
        public DataTable GetFlowList()
        {
            string strSql = string.Empty;
            strSql = @"select flowtype keyvalue,id keycode from xt_workflow_define where ispub='1' and isdelete='0'";
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 判断该用户是否有可操作的流程
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091113-->
        public static bool GetWorkPower(string UserID)
        {
            string strSql = string.Format(@"select SERIAL_NAME from SYS_USER_SERIAL 
                            where is_pass=1 and UserID='{0}'", UserID);
            string strReturn = SysParams.OAConnection().GetValue(strSql);
            return !string.IsNullOrEmpty(strReturn) ? true : false;
        }

        /// <summary>
        /// 根据用户ID与流程名称,判断该用户是否有操作该流程的权限
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <param name="FlowName">流程名称</param>
        /// <returns>true:有权限</returns>
        /// <!--addby zhongjian 20091104-->
        public static bool GetPower(string UserID, string FlowName)
        {
            string strSql = string.Format(@"select SERIAL_NAME from SYS_USER_SERIAL 
                            where is_pass=1 and UserID='{0}' and SERIAL_NAME='{1}' ", UserID, FlowName);

            string strReturn = SysParams.OAConnection().GetValue(strSql);
            return !string.IsNullOrEmpty(strReturn) ? true : false;
        }

        /// <summary>
        /// 获取流程权限的附件信息
        /// </summary>
        /// <param name="strId">附件ID</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091221-->
        public DataTable GetUserSerialAtt(string strId)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select id,filedata, filename,filetype
                            from sys_user_serialatt where id= '{0}'", strId);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 获取权限申请信息
        /// </summary>
        /// <param name="SerialID">ID</param>
        /// <returns></returns>
        public DataTable GetUserSerial(string SerialID)
        {
            DataTable dtTemp;
            string strSql = string.Format(@"select a.id,a.userid,b.username,a.serial_name,a.notes,a.is_pass,a.serial_time 
                from sys_user_serial a left join sys_user b on a.userid = b.userid where a.id='{0}'", SerialID);

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

    }
}
